﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;



using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBPasteDTSTableAdapters;

namespace BusinessLogicLayer
{
    public class GCGBPasteBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBPaste; } }

        private UserInformation userOrganization;

        private GCGBPasteMaster gcgbPasteMaster;

        private MyBindingList<GCGBPasteCollection> gcgbPasteCollectionList;

        private bool ReadonlyMode { get; set; }

        public GCGBPasteBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbPasteMaster = new GCGBPasteMaster();

                this.gcgbPasteCollectionList = new MyBindingList<GCGBPasteCollection>();


                this.arrayFilterDescription = new string[1, 2];


                GlobalDefaultValue.Apply(this);


                this.GCGBPasteMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBPasteMaster_PropertyChanged);
                this.gcgbPasteCollectionList.ListChanged += new ListChangedEventHandler(GCGBPasteCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBPasteMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "GCGBCommandID"))
            {
                this.StopTracking();
                this.GCGBPasteGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void GCGBPasteCollection_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private GCGBPasteListingTableAdapter listingTableAdapter;
        protected GCGBPasteListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBPasteListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBPasteMasterTableAdapter masterTableAdapter;
        protected GCGBPasteMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBPasteMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        GCGBPasteCollectionTableAdapter collectionTableAdapter;
        protected GCGBPasteCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBPasteCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }


        GCGBPasteGetDetailTableAdapter getDetailTableAdapter;
        protected GCGBPasteGetDetailTableAdapter GetDetailTableAdapter
        {
            get
            {
                if (getDetailTableAdapter == null) getDetailTableAdapter = new GCGBPasteGetDetailTableAdapter();
                return getDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBPasteDTS.GCGBPasteListingDataTable GCGBPasteListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBPasteMaster GCGBPasteMaster
        {
            get { return this.gcgbPasteMaster; }
        }

        public MyBindingList<GCGBPasteCollection> GCGBPasteCollectionList
        {
            get { return this.gcgbPasteCollectionList; }
        }




        private string[,] arrayFilterDescription;

        /// <summary>
        /// This get user friendly filter description from the arrayFilterDescription, BUT the arrayFilterDescription is only updated by this.GetFilterMarketingProgramID()
        /// So: To use this FilterDescription, please ONLY TAKE IT AFTER SOME METHED HAS CALLED This.GetFilterMarketingProgramID()
        /// </summary>
        /// <returns></returns>
        public string[,] FilterDescription()
        {
            return arrayFilterDescription;
        }

        public DataTable GetGCGBPasteListingDetail()
        {
            arrayFilterDescription[0, 0] = "Date"; arrayFilterDescription[0, 1] = GlobalVariables.GlobalOptionSetting.LowerFillterDate.ToString("dd/MM/yyyy") + " -> " + GlobalVariables.GlobalOptionSetting.UpperFillterDate.ToString("dd/MM/yyyy");


            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@LowerFillterDate", GlobalVariables.GlobalOptionSetting.LowerFillterDate); sqlParameter[0].SqlDbType = SqlDbType.DateTime; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@UpperFillterDate", GlobalVariables.GlobalOptionSetting.UpperFillterDate); sqlParameter[1].SqlDbType = SqlDbType.DateTime; sqlParameter[1].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("GCGBPasteListingDetail", CommandType.StoredProcedure, sqlParameter);
        }

        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBPasteID   //Primary Key
        {
            get { return this.GCGBPasteMaster.GCGBPasteID; }
            private set
            {
                if (this.GCGBPasteMaster.GCGBPasteID != value)
                {
                    this.StopTracking();

                    this.GCGBPasteMaster.GCGBPasteID = value;

                    this.GCGBPasteGetMaster();
                    this.GCGBPasteGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void GCGBPasteGetMaster()
        {
            if (this.GCGBPasteID > 0)
            {
                GCGBPasteDTS.GCGBPasteMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBPasteID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBPasteMaster.StopTracking();

                    this.GCGBPasteMaster.GCGBCommandID = masterDataTable[0].GCGBCommandID;
                    this.GCGBPasteMaster.GCGBCommandReference = masterDataTable[0].GCGBCommandReference;
                    this.GCGBPasteMaster.GCGBCommandDate = masterDataTable[0].GCGBCommandDate;

                    this.GCGBPasteMaster.GCGBPasteDate = masterDataTable[0].GCGBPasteDate;
                    this.GCGBPasteMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.GCGBPasteMaster.GCGBPasteReference = masterDataTable[0].GCGBPasteReference;
                    this.GCGBPasteMaster.Description = masterDataTable[0].Description;

                    this.GCGBPasteMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBPasteMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;

                    this.GCGBPasteMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBPasteMaster.StartTracking();

                    this.GCGBPasteMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBPasteMaster);
                this.GCGBPasteMaster.GCGBPasteDate = DateTime.Today;
                this.GCGBPasteMaster.EntryDate = DateTime.Today;
                this.GCGBPasteMaster.Reset();
            }
        }


        private void GCGBPasteGetDetail()
        {
            this.gcgbPasteCollectionList.RaiseListChangedEvents = false;
            this.gcgbPasteCollectionList.Clear();
            if (this.GCGBPasteID >= 0)
            {
                if (this.GCGBPasteMaster.GCGBCommandID > 0)
                {
                    GCGBPasteDTS.GCGBPasteGetDetailDataTable gcgbPasteGetDetailDataTable = this.GetDetailTableAdapter.GetData(this.GCGBPasteID, this.GCGBPasteMaster.GCGBCommandID, this.ReadonlyMode);

                    if (gcgbPasteGetDetailDataTable.Rows.Count > 0)
                    {
                        int gcgbCommandID = 0; int gcgbCommandSerialID = 0;
                        foreach (GCGBPasteDTS.GCGBPasteGetDetailRow gcgbPasteGetDetailRow in gcgbPasteGetDetailDataTable.Rows)
                        {
                            if (gcgbPasteGetDetailRow.RSC == 0 || gcgbCommandID != gcgbPasteGetDetailRow.GCGBCommandID || gcgbCommandSerialID != gcgbPasteGetDetailRow.GCGBCommandSerialID)
                            {
                                gcgbCommandID = gcgbPasteGetDetailRow.GCGBCommandID; gcgbCommandSerialID = gcgbPasteGetDetailRow.GCGBCommandSerialID;
                                this.gcgbPasteCollectionList.Add(new GCGBPasteCollection(gcgbPasteGetDetailRow.GCGBCommandID, gcgbPasteGetDetailRow.GCGBCommandSerialID, gcgbPasteGetDetailRow.GCGBCommandDate, gcgbPasteGetDetailRow.WorkerNameID, gcgbPasteGetDetailRow.MaterialSemiID, gcgbPasteGetDetailRow.RemainsQuantity, gcgbPasteGetDetailRow.Quantity, gcgbPasteGetDetailRow.QuantityLoss, gcgbPasteGetDetailRow.QuantityBad, gcgbPasteGetDetailRow.Remarks));
                            }
                        }
                    }
                }
            }

            this.gcgbPasteCollectionList.RaiseListChangedEvents = true;
            this.gcgbPasteCollectionList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBPasteCollection> inValidGCGBPasteCollection = this.GCGBPasteCollectionList.Where(gcgbPasteCollection => !gcgbPasteCollection.IsValid).ToList();
                return this.GCGBPasteMaster.IsValid && inValidGCGBPasteCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbPasteMaster.GCGBPasteDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBPasteApproved", this.GCGBPasteID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPasteEditable", this.GCGBPasteID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbPasteMaster.GCGBPasteDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBPasteVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPasteEditable", this.GCGBPasteID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbPasteMaster.GCGBPasteDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBPasteUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPasteEditable", this.GCGBPasteID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbPasteID)
        {
            this.ReadonlyMode = true;
            if (this.GCGBPasteID == gcgbPasteID) this.GCGBPasteID = -1; //Enforce to reload
            this.GCGBPasteID = gcgbPasteID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.GCGBPasteID == 0) this.GCGBPasteID = -1;
            this.GCGBPasteID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int gcgbPasteID = this.GCGBPasteID;

            this.GCGBPasteID = -1;
            this.GCGBPasteID = gcgbPasteID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbPasteID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbPasteID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbPasteID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }

                this.Fill(gcgbPasteID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbPasteMaster.GCGBPasteID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbPasteMaster.GCGBPasteID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbPasteMaster.GCGBPasteID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBPasteMaster.PreparedEmployeeID, this.GCGBPasteMaster.GCGBPasteDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbPasteID)
        {
            GCGBPasteDTS.GCGBPasteMasterDataTable masterDataTable;
            GCGBPasteDTS.GCGBPasteMasterRow masterRow;

            if (this.gcgbPasteMaster.GCGBPasteID <= 0) //Add
            {
                masterDataTable = new GCGBPasteDTS.GCGBPasteMasterDataTable();
                masterRow = masterDataTable.NewGCGBPasteMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbPasteMaster.GCGBPasteID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbPasteMaster.GCGBPasteID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.GCGBCommandID = this.gcgbPasteMaster.GCGBCommandID;

            masterRow.GCGBPasteDate = this.gcgbPasteMaster.GCGBPasteDate;
            masterRow.EntryDate = this.gcgbPasteMaster.EntryDate;

            masterRow.GCGBCommandReference = this.gcgbPasteMaster.GCGBCommandReference;
            masterRow.GCGBCommandDate = this.gcgbPasteMaster.GCGBCommandDate;

            masterRow.GCGBPasteReference = this.gcgbPasteMaster.GCGBPasteReference;
            masterRow.Description = this.gcgbPasteMaster.Description;

            masterRow.GCGBCommandTypeID = 1;

            masterRow.PreparedEmployeeID = this.gcgbPasteMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbPasteMaster.ApprovedEmployeeID;

            masterRow.Remarks = this.gcgbPasteMaster.Remarks;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            masterRow.Approved = false;

            if (this.gcgbPasteMaster.GCGBPasteID <= 0) masterDataTable.AddGCGBPasteMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbPasteID = masterRow.GCGBPasteID;

            return rowsAffected == 1;

        }

        private bool SaveDetail(int gcgbPasteID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsPayment = 0;

            GCGBPasteDTS.GCGBPasteCollectionDataTable collectionDataTable = new GCGBPasteDTS.GCGBPasteCollectionDataTable();

            foreach (GCGBPasteCollection gcgbPasteCollection in this.gcgbPasteCollectionList)
            {
                if (gcgbPasteCollection.Quantity > 0)
                {
                    rowsPayment++;

                    GCGBPasteDTS.GCGBPasteCollectionRow collectionRow = collectionDataTable.NewGCGBPasteCollectionRow();

                    collectionRow.GCGBPasteID = gcgbPasteID;
                    collectionRow.SerialID = ++serialID;

                    collectionRow.GCGBPasteReference = this.gcgbPasteMaster.GCGBPasteReference;
                    collectionRow.GCGBPasteDate = this.gcgbPasteMaster.GCGBPasteDate;

                    collectionRow.GCGBCommandID = gcgbPasteCollection.GCGBCommandID;
                    collectionRow.GCGBCommandSerialID = gcgbPasteCollection.GCGBCommandSerialID;

                    collectionRow.GCGBCommandTypeID = 1;

                    collectionRow.WorkerNameID = gcgbPasteCollection.WorkerNameID;

                    collectionRow.MaterialSemiID = gcgbPasteCollection.MaterialSemiID;

                    collectionRow.Quantity = gcgbPasteCollection.Quantity;
                    collectionRow.QuantityLoss = gcgbPasteCollection.QuantityLoss;
                    collectionRow.QuantityBad = gcgbPasteCollection.QuantityBad;
                  

                    collectionRow.Remarks = gcgbPasteCollection.Remarks;

                    collectionRow.Approved = false;
                    collectionRow.InActive = false;

                    collectionDataTable.AddGCGBPasteCollectionRow(collectionRow);
                }
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save detail");


            int rowsUpdated = this.CollectionTableAdapter.GCGBPasteSaveUpdate(gcgbPasteID, (int)GlobalMsADO.UpdateOption.SaveUpdate);
            if (rowsUpdated > rowsPayment) throw new System.ArgumentException("Insufficient save", "Save update");


        #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int gcgbPasteID)
        {
            int rowsUpdated = this.CollectionTableAdapter.GCGBPasteSaveUpdate(gcgbPasteID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.CollectionTableAdapter.Delete(gcgbPasteID);

            if (rowsUpdated > rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo");

            return true;
        }


        private bool SaveConflict()
        {
            return GlobalDataObjectAction.SaveConflict("GCGBPasteSaveConflict", this.GCGBPasteID);
        }



        #region Import Excel

        public bool ImportGCGBPaste(string fileName)
        {
            try
            {
                if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict"); // || !this.IsDirty

                double paymentAmount = 0;
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBPaste, fileName);
                if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                {
                    //Step 1: Check all rows to validate imported data
                    foreach (DataRow excelDataRow in excelDataTable.Rows)
                    {
                        if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                        {
                            if (paymentAmount > 0)
                            {
                                GCGBPasteCollection gcgbPasteCollection = this.GCGBPasteCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBPasteCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                if (gcgbPasteCollection == null || gcgbPasteCollection.RemainsQuantity < Math.Round(gcgbPasteCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                    exceptionTable.AddException(new string[] { gcgbPasteCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                            }
                        }
                        else
                            exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                    }

                    //Step 2: Start import
                    if (exceptionTable.Table.Rows.Count <= 0)
                    {
                        this.GCGBPasteCollectionList.RaiseListChangedEvents = false;

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                            {
                                if (paymentAmount > 0)
                                {
                                    GCGBPasteCollection gcgbPasteCollection = this.GCGBPasteCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBPasteCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                    if (gcgbPasteCollection == null || gcgbPasteCollection.RemainsQuantity < Math.Round(gcgbPasteCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                        exceptionTable.AddException(new string[] { gcgbPasteCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                                    else
                                    {
                                        gcgbPasteCollection.Quantity = gcgbPasteCollection.Quantity + paymentAmount;
                                        gcgbPasteCollection.Remarks = excelDataRow["Remarks"].ToString();
                                    }
                                }
                            }
                            else
                                exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                        }

                        this.GCGBPasteCollectionList.RaiseListChangedEvents = true;
                        this.GCGBPasteCollectionList.ResetBindings();
                    }

                    if (exceptionTable.Table.Rows.Count > 0)
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);
                }
                return true;
            }
            catch (System.Exception exception)
            {
                this.GCGBPasteCollectionList.RaiseListChangedEvents = true;
                throw exception;
            }
        }

        #endregion Import Excel



        private void RestoreProcedure()
        {

            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          LEFT(DATENAME(Month, GCGBPasteMaster.GCGBPasteDate), 3) + ', ' + DATENAME(Year, GCGBPasteMaster.GCGBPasteDate) AS Month, * " + "\r\n";
            queryString = queryString + "       FROM            GCGBPasteMaster " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPasteMaster.GCGBPasteDate >= @LowerFillterDate AND GCGBPasteMaster.GCGBPasteDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteListing", queryString);



            queryString = " @GCGBPasteID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteMaster.GCGBPasteID, GCGBPasteMaster.GCGBPasteDate, GCGBPasteMaster.GCGBPasteReference, GCGBPasteMaster.GCGBCommandID, GCGBCommandMaster.GCGBCommandReference, GCGBCommandMaster.GCGBCommandDate, GCGBPasteMaster.GCGBCommandTypeID, GCGBPasteMaster.EntryDate, GCGBPasteMaster.PreparedEmployeeID, GCGBPasteMaster.ApprovedEmployeeID, GCGBPasteMaster.Description, GCGBPasteMaster.Remarks, GCGBPasteMaster.UserID, GCGBPasteMaster.UserOrganizationID, GCGBPasteMaster.Approved " + "\r\n";
            queryString = queryString + "       FROM            GCGBPasteMaster INNER JOIN GCGBCommandMaster ON GCGBPasteMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPasteID = @GCGBPasteID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteMasterSelect", queryString);


            queryString = " @GCGBPasteDate datetime, @GCGBPasteReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBPasteMaster (GCGBPasteDate, GCGBPasteReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved) VALUES (@GCGBPasteDate, @GCGBPasteReference, @GCGBCommandID, @GCGBCommandTypeID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @Description, @Remarks, @UserID, @UserOrganizationID, @Approved) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteID, GCGBPasteDate, GCGBPasteReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBPasteMaster WHERE (GCGBPasteID = SCOPE_IDENTITY()) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteMasterInsert", queryString);


            queryString = " @GCGBPasteID int, @GCGBPasteDate datetime, @GCGBPasteReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBPasteMaster SET GCGBPasteDate = @GCGBPasteDate, GCGBPasteReference = @GCGBPasteReference, GCGBCommandID = @GCGBCommandID, GCGBCommandTypeID = @GCGBCommandTypeID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Description = @Description, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved WHERE GCGBPasteID = @GCGBPasteID " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteID, GCGBPasteDate, GCGBPasteReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBPasteMaster WHERE (GCGBPasteID = @GCGBPasteID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteMasterUpdate", queryString);


            queryString = " @GCGBPasteID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBPasteMaster WHERE GCGBPasteID = @GCGBPasteID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteMasterDelete", queryString);




            queryString = " @GCGBPasteID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteID, SerialID, GCGBPasteReference, GCGBPasteDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSemiID, Quantity, QuantityLoss, QuantityBad, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM            GCGBPasteCollection " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPasteID = @GCGBPasteID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteCollectionSelect", queryString);


            queryString = " @GCGBPasteID int, @SerialID int, @GCGBPasteReference nvarchar(50), @GCGBPasteDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSemiID int, @Quantity float, @QuantityLoss float, @QuantityBad float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBPasteCollection (GCGBPasteID, SerialID, GCGBPasteReference, GCGBPasteDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSemiID, Quantity, QuantityLoss, QuantityBad, Remarks, Approved, InActive) " + "\r\n";
            queryString = queryString + "                   VALUES (@GCGBPasteID, @SerialID, @GCGBPasteReference, @GCGBPasteDate, @GCGBCommandID, @GCGBCommandSerialID, @GCGBCommandTypeID, @WorkerNameID, @MaterialSemiID, @Quantity, @QuantityLoss, @QuantityBad, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteID, SerialID, GCGBPasteReference, GCGBPasteDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSemiID, Quantity, QuantityLoss, QuantityBad, Remarks, Approved, InActive FROM GCGBPasteCollection WHERE (GCGBPasteID = @GCGBPasteID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteCollectionInsert", queryString);


            queryString = " @GCGBPasteID int, @SerialID int, @GCGBPasteReference nvarchar(50), @GCGBPasteDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSemiID int, @Quantity float, @QuantityLoss float, @QuantityBad float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBPasteCollection SET GCGBPasteReference = @GCGBPasteReference, GCGBPasteDate = @GCGBPasteDate, GCGBCommandID = @GCGBCommandID, GCGBCommandSerialID = @GCGBCommandSerialID, GCGBCommandTypeID = @GCGBCommandTypeID, WorkerNameID = @WorkerNameID, MaterialSemiID = @MaterialSemiID, Quantity = @Quantity, QuantityLoss = @QuantityLoss, QuantityBad = @QuantityBad, " + "\r\n";
            queryString = queryString + "                  Remarks = @Remarks, Approved = @Approved, InActive = @InActive WHERE (GCGBPasteID = @GCGBPasteID) AND (SerialID = @SerialID) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPasteID, SerialID, GCGBPasteReference, GCGBPasteDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSemiID, Quantity, QuantityLoss, QuantityBad, Remarks, Approved, InActive FROM GCGBPasteCollection WHERE (GCGBPasteID = @GCGBPasteID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteCollectionUpdate", queryString);

            queryString = " @GCGBPasteID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBPasteCollection WHERE GCGBPasteID = @GCGBPasteID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteCollectionDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          GCGBCommandDetail.GCGBCommandID, GCGBCommandDetail.SerialID AS GCGBCommandSerialID, GCGBCommandDetail.GCGBCommandDate, GCGBPasteCollection.WorkerNameID, GCGBCommandDetail.MaterialSemiID, ROUND(GCGBCommandDetail.QuantitySemi - GCGBCommandDetail.QuantityPaste - GCGBCommandDetail.QuantityPasteLoss - GCGBCommandDetail.QuantityPasteBad + GCGBPasteCollection.Quantity + GCGBPasteCollection.QuantityLoss +  GCGBPasteCollection.QuantityBad, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, GCGBPasteCollection.Quantity, GCGBPasteCollection.QuantityLoss, GCGBPasteCollection.QuantityBad, GCGBPasteCollection.Remarks, 0 AS RSC " + "\r\n";
            queryEdit = queryEdit + "   FROM            GCGBCommandDetail INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBPasteCollection ON GCGBCommandDetail.GCGBCommandID = GCGBPasteCollection.GCGBCommandID AND GCGBCommandDetail.SerialID = GCGBPasteCollection.GCGBCommandSerialID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandDetail.GCGBCommandID) AND GCGBPasteCollection.GCGBPasteID = @GCGBPasteID " + "\r\n";

            queryNew = "                SELECT          GCGBCommandDetail.GCGBCommandID, GCGBCommandDetail.SerialID AS GCGBCommandSerialID, GCGBCommandDetail.GCGBCommandDate, 0 AS WorkerNameID, GCGBCommandDetail.MaterialSemiID, ROUND(GCGBCommandDetail.QuantitySemi - GCGBCommandDetail.QuantityPaste - GCGBCommandDetail.QuantityPasteLoss - GCGBCommandDetail.QuantityPasteBad, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, 0 AS Quantity, 0 AS QuantityLoss, 0 AS QuantityBad, GCGBCommandDetail.Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            GCGBCommandDetail " + "\r\n";
            queryNew = queryNew + "     WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandDetail.GCGBCommandID) AND GCGBCommandDetail.Approved = 1 AND ROUND(GCGBCommandDetail.QuantitySemi - GCGBCommandDetail.QuantityPasteLoss - GCGBCommandDetail.QuantityPasteBad, " + GlobalVariables.Round0Amount.ToString() + ") > 0 " + "\r\n";

            queryString = " @GCGBPasteID Int, @GCGBCommandID Int, @ReadonlyMode bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@GCGBPasteID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY GCGBCommandDetail.GCGBCommandDate, GCGBCommandDetail.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandDetail.GCGBCommandDate, GCGBCommandDetail.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandDetail.GCGBCommandDate, GCGBCommandDetail.GCGBCommandID, GCGBCommandSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBPasteGetDetail", queryString);



            queryString = " @GCGBPasteID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBCommandDetail " + "\r\n";
            queryString = queryString + "       SET             GCGBCommandDetail.QuantityPaste = ROUND(GCGBCommandDetail.QuantityPaste + GCGBPasteCollection.Quantity * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "),  " + "\r\n";
            queryString = queryString + "                  GCGBCommandDetail.QuantityPasteLoss = ROUND(GCGBCommandDetail.QuantityPasteLoss + GCGBPasteCollection.QuantityLoss * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                  GCGBCommandDetail.QuantityPasteBad = ROUND(GCGBCommandDetail.QuantityPasteBad + GCGBPasteCollection.QuantityBad * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ") " + "\r\n";
            queryString = queryString + "       FROM           GCGBPasteCollection INNER JOIN " + "\r\n";
            queryString = queryString + "                       GCGBCommandDetail ON GCGBPasteCollection.GCGBCommandID = GCGBCommandDetail.GCGBCommandID AND GCGBPasteCollection.GCGBCommandSerialID = GCGBCommandDetail.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPasteSaveUpdate", queryString);




            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1  GCGBPasteMaster.GCGBPasteID FROM GCGBPasteMaster INNER JOIN GCGBCommandMaster ON GCGBPasteMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID AND GCGBPasteMaster.GCGBPasteDate < GCGBCommandMaster.GCGBCommandDate ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPasteSaveConflict", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 GCGBPasteID FROM GCGBPasteMaster WHERE GCGBPasteID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPasteApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPasteEditable");// queryArray);


        }

    }    
}
